package com.yst.suivi.services.assignation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;

import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.FinderException;
import javax.ejb.RemoveException;
import javax.ejb.SessionBean;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.yst.suivi.comparator.NameObjectComparator;
import com.yst.suivi.data.assignation.Assignation;
import com.yst.suivi.data.assignation.AssignationUtil;
import com.yst.suivi.data.project.Project;
import com.yst.suivi.data.project.ProjectUtil;
import com.yst.suivi.data.team.TeamRole;
import com.yst.suivi.data.team.TeamRoleUtil;
import com.yst.suivi.data.user.User;
import com.yst.suivi.data.user.UserUtil;
import com.yst.suivi.services.AbstractSessionBean;

/**
 * @ejb.bean display-name="AssignationService" jndi-name="AssignationHomeRemote"
 *           name="AssignationView" type="Stateless" view-type="both"
 */
public class AssignationServiceBean extends AbstractSessionBean implements SessionBean
{
	/**
	 * @throws CreateException
	 * @ejb.create-method view-type = "both"
	 */
	public void ejbCreate() throws CreateException
	{
		// by default nothing to do
	}

	/**
	 * This method create a new Contrat.
	 * 
	 * @param standard
	 * @return standard updated
	 * @throws CreateException
	 * @throws FinderException
	 * @throws RemoveException
	 * @throws EJBException
	 * @throws SQLException
	 * @ejb.interface-method view-type = "both"
	 * @ejb.transaction type = "Required"
	 */
	public void affectTeam(String idProject, HashMap teams) throws NamingException, CreateException, FinderException, EJBException, RemoveException, SQLException
	{
		Project project = ProjectUtil.getLocalHome().findByPrimaryKey(idProject);
		// delete all the existing team.
		Connection conn = null;
		InitialContext ctx = new InitialContext();
		DataSource ds = (DataSource) ctx.lookup(DB_JNDI_NAME);
		conn = ds.getConnection();
		StringBuffer request = new StringBuffer();
		request.append("delete from assignation where prjid='").append(idProject).append("'");
		Statement stmt = conn.createStatement();
		stmt.execute(request.toString());
		stmt.close();
		conn.close();
		// create the new team
		Assignation assign;
		TeamRole role;
		Iterator it = teams.keySet().iterator();
		String key;
		ArrayList lstUser;
		User usr;
		while (it.hasNext())
		{
			key = it.next().toString();
			role = TeamRoleUtil.getLocalHome().findByPrimaryKey(key);
			lstUser = (ArrayList) teams.get(key);
			for (int i = 0; i < lstUser.size(); i++)
			{
				usr = (User) lstUser.get(i);
				assign = AssignationUtil.getLocalHome().create();
				assign.setTeamRole(role);
				assign.setProject(project);
				assign.setUser(usr);
			}
		}
	}

	/**
	 * This method returns the list of all the user affected to the role.
	 * 
	 * @param standard
	 * @return standard updated
	 * @throws SQLException
	 * @throws NamingException
	 * @throws FinderException
	 * @ejb.interface-method view-type = "both"
	 * @ejb.transaction type = "Required"
	 */
	public Collection getTeam(Project prj, TeamRole team) throws SQLException, NamingException, FinderException
	{
		ArrayList retour = new ArrayList();
		Connection conn = null;
		InitialContext ctx = new InitialContext();
		DataSource ds = (DataSource) ctx.lookup(DB_JNDI_NAME);
		conn = ds.getConnection();
		StringBuffer request = new StringBuffer();
		request.append("select userid from Assignation where prjid='").append(prj.getId()).append("' and teamroleid='").append(team.getId()).append("'");
		PreparedStatement ps = conn.prepareStatement(request.toString());
		ResultSet rs = ps.executeQuery();
		while (rs.next())
		{
			retour.add(UserUtil.getLocalHome().findByPrimaryKey(rs.getString("userid")));
		}
		rs.close();
		ps.close();
		conn.close();
		return retour;
	}

	/**
	 * This method returns the list of all the user affected to the role.
	 * 
	 * @param standard
	 * @return standard updated
	 * @throws CreateException
	 * @throws FinderException
	 * @throws RemoveException
	 * @throws EJBException
	 * @throws SQLException
	 * @ejb.interface-method view-type = "both"
	 * @ejb.transaction type = "Required"
	 */
	public Collection getTeam(Project prj) throws NamingException, CreateException, FinderException, EJBException, RemoveException, SQLException
	{
		ArrayList retour = new ArrayList();
		Connection conn = null;
		InitialContext ctx = new InitialContext();
		DataSource ds = (DataSource) ctx.lookup(DB_JNDI_NAME);
		conn = ds.getConnection();
		StringBuffer request = new StringBuffer();
		request.append("select userid from Assignation where prjid='").append(prj.getId()).append("'");
		PreparedStatement ps = conn.prepareStatement(request.toString());
		ResultSet rs = ps.executeQuery();
		while (rs.next())
		{
			retour.add(UserUtil.getLocalHome().findByPrimaryKey(rs.getString("userid")));
		}
		rs.close();
		ps.close();
		conn.close();
		return retour;
	}

	/**
	 * This method returns the list of all the user affected to the role.
	 * 
	 * @param standard
	 * @return standard updated
	 * @throws CreateException
	 * @throws FinderException
	 * @throws RemoveException
	 * @throws EJBException
	 * @throws SQLException
	 * @ejb.interface-method view-type = "both"
	 * @ejb.transaction type = "Required"
	 */
	public Collection getProjectFor(User usr) throws NamingException, CreateException, FinderException, EJBException, RemoveException, SQLException
	{
		ArrayList retour = new ArrayList();
		Connection conn = null;
		InitialContext ctx = new InitialContext();
		DataSource ds = (DataSource) ctx.lookup(DB_JNDI_NAME);
		conn = ds.getConnection();
		StringBuffer request = new StringBuffer();
		request.append("select prjid from Assignation where userid='").append(usr.getId()).append("'");
		PreparedStatement ps = conn.prepareStatement(request.toString());
		ResultSet rs = ps.executeQuery();
		while (rs.next())
		{
			retour.add(ProjectUtil.getLocalHome().findByPrimaryKey(rs.getString("prjid")));
		}
		rs.close();
		ps.close();
		conn.close();
		Collections.sort(retour, NameObjectComparator.comparator);
		return retour;
	}

	/**
	 * 
	 * @param usr
	 * @param prjId
	 * @return
	 * @ejb.interface-method view-type = "both"
	 */
	public boolean isUserInProject(User usr, String prjId)
	{
		boolean retour = false;
		try
		{
			Connection conn = null;
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup(DB_JNDI_NAME);
			conn = ds.getConnection();
			StringBuffer request = new StringBuffer();
			request.append("select prjid from Assignation where userid='").append(usr.getId()).append("'");
			PreparedStatement ps = conn.prepareStatement(request.toString());
			ResultSet rs = ps.executeQuery();
			retour = rs.next();
			rs.close();
			ps.close();
			conn.close();
		}
		catch (Throwable e)
		{
			// if error occurs, not in project
			retour = false;
		}
		return retour;
	}

	/**
	 * @ejb.interface-method view-type = "both"
	 * @param usr
	 * @param prjId
	 * @param role
	 * @return
	 */
	public boolean isUserInRole(User usr, String prjId, String roleId)
	{
		boolean isInRole = false;
		try
		{
			Connection conn = null;
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup(DB_JNDI_NAME);
			conn = ds.getConnection();
			StringBuffer request = new StringBuffer();
			request.append("select * from Assignation where userid='").append(usr.getId()).append("' and prjid='").append(prjId).append("' and teamroleid='").append(roleId).append("'");
			PreparedStatement ps = conn.prepareStatement(request.toString());
			ResultSet rs = ps.executeQuery();
			isInRole = rs.next();
			rs.close();
			ps.close();
			conn.close();
		}
		catch (Throwable e)
		{
			// if error occurs, not in project
			isInRole = false;
		}
		return isInRole;
	}
}
